package com.example.autoconfig.app.jdbc;

import com.example.autoconfig.app.jdbc.entity.OrderEntity;
import com.example.autoconfig.app.jdbc.entity.OrderItemEntity;
import com.example.autoconfig.app.jdbc.entity.UserEntity;
import com.example.autoconfig.app.jdbc.respository.OrderItemsRepository;
import com.example.autoconfig.app.jdbc.respository.OrderRepository;
import com.example.autoconfig.app.jdbc.respository.UserRepository;
import com.zaxxer.hikari.HikariDataSource;

import org.apache.shardingsphere.transaction.annotation.ShardingTransactionType;
import org.apache.shardingsphere.transaction.core.TransactionType;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.stereotype.Controller;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Optional;

import javax.sql.DataSource;

/**
 * @author fangzhuangzhan
 * @date 2021/09/21 22:03
 */
@Controller
@RequestMapping("/sharding")
public class ShardingsphereController {


    // encryptDataSource   (SpringBootConfiguration.class)
    // masterSlaveDataSource   (SpringBootConfiguration.class)
    // shadowDataSource   (SpringBootConfiguration.class)
    // shardingDataSource   (SpringBootConfiguration.class)

    /*@Autowired
    @Qualifier("shardingDataSource")*/
    DataSource dataSource;

    @Autowired
    private OrderRepository orderRepository;

    public static void main(String[] args) {
        System.out.println(0%2);
        System.out.println(1%2);
        System.out.println(1254293189411441665L%3);
    }

    @Autowired
    private OrderItemsRepository orderItemsRepository;

    @GetMapping("testTx")
    @Transactional
    @ShardingTransactionType(TransactionType.XA)
    public String testTx(Integer success){
        // 读取两个库的记录做修改
        for (int i = 0; i < 2; i++) {
            OrderItemEntity orderItemEntity =new OrderItemEntity();
            orderItemEntity.setOrderNum(Long.valueOf(i));
            orderItemEntity.setItemNum(i+"");
            orderItemEntity.setItemPrice(BigDecimal.valueOf(99999));
            orderItemEntity.setItemCount(BigDecimal.valueOf(i));
            orderItemsRepository.save(orderItemEntity);
        }
        // 如果seccess = null 则抛异常回滚
        int ex = success/2;
        return "success";
    }

    @GetMapping("/orderIterm")
    public String orderIterm(){

        for (int i = 0; i < 10; i++) {
            OrderItemEntity orderItemEntity =new OrderItemEntity();
            orderItemEntity.setOrderNum(Long.valueOf(i));
            orderItemEntity.setItemNum(i+"");
            orderItemEntity.setItemPrice(BigDecimal.valueOf(i));
            orderItemEntity.setItemCount(BigDecimal.valueOf(i));
            orderItemsRepository.save(orderItemEntity);
        }

        return "success";
    }


    @Autowired
    private UserRepository userRepository;

    @GetMapping("/user")
    public String user(){

        for (int i = 0; i < 4; i++) {
            UserEntity userEntity = new UserEntity();
            userEntity.setNickName("nickName:"+i);
            userEntity.setUserNum("nickNum:"+i);
            userRepository.save(userEntity);
        }

        return "success";
    }



    @GetMapping("/partion")
    public String partion(){
        for (int i = 0; i <50; i++) {
            final OrderEntity orderEntity = new OrderEntity();

            // orderEntity.setId(Long.valueOf(i+""));
            orderEntity.setOrderNo("orderNo"+i);
            orderEntity.setClientId(Long.valueOf(i+""));
            orderEntity.setAddress("address"+i);
            orderEntity.setCreateTime(System.currentTimeMillis());
            orderEntity.setState("init");
            System.out.println(orderEntity.toString());

            orderRepository.save(orderEntity);
        }
        return "success";
    }

    @GetMapping("/getAllBySharding")
    public String getAllBySharding(){
        final List<OrderEntity> all = orderRepository.findAll();
        System.out.println(all);
        return "success";
    }

    @GetMapping("/deleteBySharding")
    public String deleteBySharding(Long id){
        orderRepository.deleteById(id);
        return "success";
    }

    @GetMapping("/updateBySharding")
    public String deleteBySharding(String address,Long id){
        final Optional<OrderEntity> byId = orderRepository.findById(id);
        if (byId.isPresent()) {
            final OrderEntity orderEntityInDb = byId.get();
            orderEntityInDb.setAddress(address);
            orderRepository.save(orderEntityInDb);
        }
        return "success";
    }

    @GetMapping("/save")
    public String get(){
        save();
        return "success";
    }
    @GetMapping("/delete")
    public String del(String name){
        delete(name);
        return "success";
    }

    @GetMapping("/queryAll")
    public String query(){
        getAll();
        return "success";
    }

    @GetMapping("/update")
    public String up(String name,@RequestParam(defaultValue = "99") Integer age){
        Student student = new Student();
        student.setAge(age);
        student.setName(name);
        update(student);
        return "success";
    }


    public void save() {
        try {
            String sql = "insert into student(name,age) values(?,?);";
            Connection con = null;
            PreparedStatement stmt = null;
            // 连接对象
            con = dataSource.getConnection();
            // 执行命令对象
            stmt =  con.prepareStatement(sql);
            stmt.setString(1,"hadeng");
            stmt.setInt(2,33);
            // 执行
            stmt.execute();
            // 关闭
            stmt.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void delete(String name) {
        try {
            String sql = "delete from student where name=?";
            Connection con = null;
            PreparedStatement stmt = null;
            // 连接对象
            con = dataSource.getConnection();
            // 执行命令对象
            stmt =  con.prepareStatement(sql);
            // 执行
            stmt.setString(1,name);
            stmt.execute();
            // 关闭
            stmt.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private int update(Student student) {
        Connection con = null;
        PreparedStatement statement = null;
        int i = 0;
        String sql = "update student set age=? where name=?";
        try {
            con = dataSource.getConnection();
            statement = con.prepareStatement(sql);
            statement.setInt(1,student.getAge());
            statement.setString(2, student.getName());
            statement.execute();
            statement.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return i;
    }

    private Integer getAll() {
        Connection conn = null;
        String sql = "select * from student";
        PreparedStatement pstmt = null;
        try {

            conn = dataSource.getConnection();

            pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery(sql);
            int col = rs.getMetaData().getColumnCount();
            System.out.println("============================");
            while (rs.next()) {
                for (int i = 1; i <= col; i++) {
                    System.out.print(rs.getString(i) + "\t");
                    if ((i == 2) && (rs.getString(i).length() < 8)) {
                        System.out.print("\t");
                    }
                }
                System.out.println("");
            }
            System.out.println("============================");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    @GetMapping("insertMillion")
    public Object insertMillionData(){

        // 使用preparedStatement插入100W订单
        try {
            final long start = System.currentTimeMillis();
            System.out.println("insert start : " + start );
            // insert into order(id,orderNo, clientId, desc, address, createTime, updateTime,state) values(1,"1","2","3","4",123123,123,"56");
            String sql = "insert into order(`id`, `orderNo`, `clientId`, `desc`, `address`, `createTime`, `updateTime`, `state`) values(?,?,?,?,?,?,?,?);";
            Connection con = null;
            PreparedStatement stmt = null;
            // 连接对象
            con = dataSource.getConnection();
            con.setAutoCommit(false);
            // 执行命令对象
            stmt =  con.prepareStatement(sql);
            for (int i = 0; i < 1; i++) {
                stmt.setInt(1,i);
                stmt.setString(2,"orderNo" + i);
                stmt.setString(3,"clientId" + i);
                stmt.setString(4,"desc" + i);
                stmt.setString(5,"address: sichuanchengdu1111");
                stmt.setLong(6,System.currentTimeMillis());
                stmt.setLong(7,System.currentTimeMillis());
                stmt.setString(8,"init");
                stmt.addBatch();
                if(i %10000 == 0){
                    stmt.executeBatch();
                    stmt.clearBatch();
                }
            }
            con.commit();
            final long end = System.currentTimeMillis();
            System.out.println("insert end : " + end );
            final long time = (end - start) / 1000;
            System.out.println("总耗时：" + time +"s");

            // 关闭
            stmt.close();
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }

        return "success";
    }
}





